package co.th.bjc.md.helper.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import co.th.bjc.md.dao.Images;
import co.th.bjc.md.dao.Product;
import co.th.bjc.md.helper.entries.HelperDatabse;
import co.th.bjc.md.helper.utils.ThaiUtil;

public class ProductHelper extends HelperDatabse{
	
	public Product getEditProduct(BigDecimal id) throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		Product prod = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement("select * from product where product_code = ?");
			stmt.setBigDecimal(1, id);
			rs = stmt.executeQuery();
			ImagesHelper image = new ImagesHelper();
			SupplierHelper supplier = new SupplierHelper();
			if (rs.next()){
				prod = new Product();
				prod.setProductCode(rs.getBigDecimal("product_code"));
				prod.setProductName(rs.getString("product_name"));
				prod.setProductDetail(rs.getString("product_detail"));
				prod.setSupplierCode(rs.getBigDecimal("supplier_code"));
				prod.setSupplier(supplier.findSupplierByID(prod.getSupplierCode()));
				prod.setActive(rs.getString("active"));
				prod.setImages(image.getImagesByProductCode(prod.getProductCode()));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (con != null) con.close();
		}
		return prod;
	}
	
	public List<Product> findProduct() throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Product product = null;
		List<Product> listProduct = new ArrayList<Product>(0);
		try {
			con = getConnection();
			stmt = con.prepareStatement(PRODUCT.FIND_ALL);
			
			rs = stmt.executeQuery();
			ImagesHelper image = new ImagesHelper();
			while (rs.next()){
				product = new Product();
				product.setProductCode(rs.getBigDecimal("product_code"));
				product.setProductName(rs.getString("product_name"));
				product.setProductDetail(rs.getString("product_detail"));
				product.setSupplierCode(rs.getBigDecimal("supplier_code"));
				product.setActive(rs.getString("active"));
				product.setCreateBy(rs.getString("create_by"));
				product.setCreateDate(new Date(rs.getTimestamp("create_date").getTime()));
				product.setUpdateBy(rs.getString("update_by"));
				product.setUpdateDate(new Date(rs.getTimestamp("update_date").getTime()));
				product.setImages(image.getImagesByProductCode(product.getProductCode()));
				
				listProduct.add(product);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (con != null) con.close();
		}
		return listProduct;
	}
	
	public List<Product> findProduct(String productName, BigDecimal supplierCode) throws SQLException{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Product product = null;
		List<Product> listProduct = new ArrayList<Product>(0);
		try {
			String hql = "SELECT * FROM PRODUCT aTable INNER JOIN SUPPLIER bTable " +
			"ON aTable.supplier_code = bTable.supplier_code " +
			"WHERE 1=1 ";
			
			if (productName != null && !"".equals(productName)){
				hql += " AND aTable.product_name like '%" + productName + "%' ";
			}
			if (supplierCode != null && supplierCode.intValue() != 0){
				hql += " AND bTable.supplier_code = '" + supplierCode+"'";
			}
			
			con = getConnection();
			stmt = con.prepareStatement(hql);
			
			rs = stmt.executeQuery();
			ImagesHelper image = new ImagesHelper();
			SupplierHelper supplier = new SupplierHelper();
			while (rs.next()){
				product = new Product();
				product.setProductCode(rs.getBigDecimal("product_code"));
				product.setProductName(rs.getString("product_name"));
				product.setProductDetail(rs.getString("product_detail"));
				product.setSupplierCode(rs.getBigDecimal("supplier_code"));
				product.setActive(rs.getString("active"));
				
				product.setImages(image.getImagesByProductCode(product.getProductCode()));
				product.setSupplier(supplier.findSupplierByID(product.getSupplierCode()));
				
				listProduct.add(product);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (con != null) con.close();
		}
		return listProduct;
	}
	
//	addProduct()
	
	public boolean delete(Connection conn, BigDecimal productCode){
		PreparedStatement stmt = null;
		try {	
			ImagesHelper imageHelper = new ImagesHelper();
			List<Images> images = imageHelper.getImagesByProductCode(productCode);
			for(Images img : images){
				imageHelper.delete(conn, img.getImageId());
			}
			stmt = conn.prepareStatement(PRODUCT.DELETE);
			stmt.setInt(1, productCode.intValue());

			int record = stmt.executeUpdate();
			return record > 0 ? true : false;
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}
	}
	
	public boolean delete(BigDecimal productCode) throws SQLException{
		Connection conn = null;
		try {	
			conn = getConnection();
			conn.setAutoCommit(true);
			return delete(conn, productCode);
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}finally{
			if (conn != null){
				conn.close();
			}
		}
	}
	
	public void insert(Product product) throws Exception{
		Connection conn = null;
		try {	
			conn = getConnection();
			conn.setAutoCommit(true);
			product = insert(conn, product);
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			if (conn != null){
				conn.close();
			}
		}
	}
	
	public Product addProduct(Connection conn, Product input) throws Exception {
		try {
			if (conn==null){
				conn = getConnection();
				conn.setAutoCommit(true);
			}

			input = insert(conn, input);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return input;
	}
	
	public List<Product> addImage(Connection conn, List<Product> input) throws Exception {
		boolean isConnNull = false;
		try {
			if (conn==null){
				conn = getConnection();
				isConnNull = true;
				conn.setAutoCommit(true);
			}
			for(Product product : input){
				product = insert(conn, product);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			if (conn != null && isConnNull){
				conn.close();
			}
		}
		return input;
	}
	
	public boolean update(Connection conn, List<Product> input) throws Exception {
		boolean isConnNull = false;
		try {
			if (conn==null){
				conn = getConnection();
				isConnNull = true;
				conn.setAutoCommit(true);
			}

			for(Product product : input){
				if (!edit(conn, product)) return false;
			}
			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}finally{
			if (conn != null && isConnNull){
				conn.close();
			}
		}
	}
	
	public boolean update(Connection conn, Product input) throws Exception {
		try {
			if (conn==null){
				conn = getConnection();
				conn.setAutoCommit(true);
			}

			return edit(conn, input);
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}
	}
	
	/* ========================================================================= */
	
	synchronized Product insert(Connection conn, Product input) throws Exception {
		PreparedStatement stmt = null;
		try {			
			input.setProductCode(getSequence(conn));
			
			stmt = conn.prepareStatement(PRODUCT.INSERT);
			stmt.setBigDecimal(1, input.getProductCode());
			stmt.setString(2, input.getProductName());
			stmt.setString(3, input.getProductDetail());
			stmt.setBigDecimal(4, input.getSupplierCode());
			stmt.setString(5, input.getActive());
			stmt.setString(6, input.getCreateBy());
			stmt.setDate(7, new java.sql.Date(input.getCreateDate().getTime()));
			stmt.setString(8, input.getUpdateBy());
			stmt.setDate(9, new java.sql.Date(input.getCreateDate().getTime()));

			stmt.executeUpdate();
			
			if (!input.getImages().isEmpty()){
				new ImagesHelper().addImage(conn, input.getImages());
			}
			
			if (input.getSupplier() != null){
				//new SupplierHelper().add);
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (stmt != null)
				stmt.close();
		}
		return input;
	}
	
	synchronized BigDecimal getSequence(Connection conn) throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		BigDecimal sequence = null;
		try {
			stmt = conn.prepareStatement(PRODUCT.SEQUENCE);
			rs = stmt.executeQuery();
			if (rs.next()){
				sequence = new BigDecimal(rs.getInt(1) + 1);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
		}
		return sequence;
	}
	
	synchronized boolean edit(Connection conn, Product input) throws Exception {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(PRODUCT.UPDATE);
			stmt.setString(1, input.getProductName());
			stmt.setString(2, input.getProductDetail());
			stmt.setBigDecimal(3, input.getSupplierCode());
			stmt.setString(4, input.getActive());
//			stmt.setString(5, input.getCreateBy());
//			stmt.setDate(6, new java.sql.Date(input.getCreateDate().getTime()));
			stmt.setString(5, input.getUpdateBy());
			stmt.setDate(6, new java.sql.Date(input.getUpdateDate().getTime()));
			
			stmt.setBigDecimal(7, input.getProductCode());

			int record = stmt.executeUpdate();
			return record > 0 ? true : false;
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
}
